﻿using System;

using System.Linq;
using System.Web;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Text;

/// <summary>
/// Summary description for MonthCardDAL
/// </summary>
public class MonthCardDAL
{
    /// <summary>
    /// ActiveAction
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public static bool ActiveAction(MonthCard month)
    {
        string strSQL = @"UPDATE MonthCard SET
                                  Active=@active
                              WHERE
                                  ID=@ID";

        //parms
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@ID",month.ID),
                        new SqlParameter("@active",month.Active)
                    };

        try
        {
            return (SqlHelper.ExecuteNonQuery(CommandType.Text, strSQL, parms) > 0);
        }
        catch (Exception)
        {
            throw;
        }
    }


    public static List<MonthCard> GetAll()
    {
        string strSQL = @"SELECT     mc.ID, mc.PlateNumber, mc.PlateTypeId, 
			                            mc.CarColorId, mc.ParkingFloorId, 
			                            mc.CustomerId, mc.FromDate, 
                                        mc.ToDate, mc.Price, mc.Active, 
                                        c.CustomerCode, c.CustomerName, 
                                        c.Email, c.Address, c.Phone
                            FROM         MonthCard mc
                            LEFT JOIN Customer c ON mc.CustomerId = c.CustomerID
                            ORDER BY mc.LastUpdate DESC";
        SqlDataReader reader;
        List<MonthCard> list = null;
        MonthCard monthCard;
        try
        {
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL);
            if (reader.HasRows)
            {
                list = new List<MonthCard>();
                while (reader.Read())
                {
                    monthCard = new MonthCard();
                    monthCard.ID = SqlHelper.CheckGuidNull(reader["ID"]);
                    monthCard.PlateNumber = SqlHelper.CheckStringNull(reader["PlateNumber"]);
                    monthCard.PlateType = new PlateType();
                    monthCard.PlateType.ID = SqlHelper.CheckIntNull(reader["PlateTypeId"]);
                    monthCard.CarColor = new CarColor();
                    monthCard.CarColor.ID = SqlHelper.CheckGuidNull(reader["CarColorId"]);
                    monthCard.ParkingFloor = new ParkingFloor();
                    monthCard.ParkingFloor.ID = SqlHelper.CheckGuidNull(reader["ParkingFloorId"]);
                    monthCard.FromDate = Convert.ToDateTime(SqlHelper.CheckDateNull(reader["FromDate"]));
                    monthCard.ToDate = Convert.ToDateTime(SqlHelper.CheckDateNull(reader["ToDate"]));
                    monthCard.Price = SqlHelper.CheckIntNull(reader["Price"]);
                    monthCard.Active = SqlHelper.CheckBooleanNull(reader["Active"]);
                    Customer customer = null;
                    if (reader["CustomerId"] != null)
                    {
                        customer = new Customer();
                        customer.CustomerID = SqlHelper.CheckGuidNull(reader["CustomerId"]);
                        customer.CustomerCode = SqlHelper.CheckStringNull(reader["CustomerCode"]);
                        customer.CustomerName = SqlHelper.CheckStringNull(reader["CustomerName"]);
                        customer.Address = SqlHelper.CheckStringNull(reader["Address"]);
                        customer.Phone = SqlHelper.CheckStringNull(reader["Phone"]);
                    }
                    monthCard.Customer = customer;
                    list.Add(monthCard);
                }
            }

            return list;
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }

    public static MonthCard GetById(Guid id)
    {
        string strSQL = @"SELECT     mc.ID, mc.PlateNumber, mc.PlateTypeId, 
			                            mc.CarColorId, mc.ParkingFloorId, 
			                            mc.CustomerId, mc.FromDate, 
                                        mc.ToDate, mc.Price, mc.Active, 
                                        c.CustomerCode, c.CustomerName, 
                                        c.Email, c.Address, c.Phone
                            FROM         MonthCard mc
                            LEFT JOIN Customer c ON mc.CustomerId = c.CustomerID
                              WHERE mc.ID=@ID";
        SqlDataReader reader;
        MonthCard monthCard=null;
        try
        {
            SqlParameter parm = new SqlParameter("@ID", id);
            reader = SqlHelper.ExecuteReader(CommandType.Text, strSQL, parm);
            if (reader.HasRows)
            {
                if (reader.Read())
                {
                    monthCard = new MonthCard();
                    monthCard.ID = SqlHelper.CheckGuidNull(reader["ID"]);
                    monthCard.PlateNumber = SqlHelper.CheckStringNull(reader["PlateNumber"]);
                    monthCard.PlateType = new PlateType();
                    monthCard.PlateType.ID = SqlHelper.CheckIntNull(reader["PlateTypeId"]);
                    monthCard.CarColor = new CarColor();
                    monthCard.CarColor.ID = SqlHelper.CheckGuidNull(reader["CarColorId"]);
                    monthCard.ParkingFloor = new ParkingFloor();
                    monthCard.ParkingFloor.ID = SqlHelper.CheckGuidNull(reader["ParkingFloorId"]);
                    monthCard.FromDate = Convert.ToDateTime(SqlHelper.CheckDateNull(reader["FromDate"]));
                    monthCard.ToDate = Convert.ToDateTime(SqlHelper.CheckDateNull(reader["ToDate"]));
                    monthCard.Price = SqlHelper.CheckIntNull(reader["Price"]);
                    monthCard.Active = SqlHelper.CheckBooleanNull(reader["Active"]);
                    Customer customer = null;
                    if (reader["CustomerId"] != null)
                    {
                        customer = new Customer();
                        customer.CustomerID = SqlHelper.CheckGuidNull(reader["CustomerId"]);
                        customer.CustomerCode = SqlHelper.CheckStringNull(reader["CustomerCode"]);
                        customer.CustomerName = SqlHelper.CheckStringNull(reader["CustomerName"]);
                        customer.Address = SqlHelper.CheckStringNull(reader["Address"]);
                        customer.Phone = SqlHelper.CheckStringNull(reader["Phone"]);
                    }
                    monthCard.Customer = customer;
                }
            }
            return monthCard;
        }
        catch (Exception ex)
        {

            throw ex;
        }
    }

    public static bool IsExistedPlateNumber(string plateNumber, Guid id)
    {
        string strSQL = @"SELECT COUNT(ID) FROM MonthCard 
                            WHERE dbo.ufn_StripCharacters(UPPER(PlateNumber), '^a-z0-9')
                            LIKE dbo.ufn_StripCharacters(UPPER(@PlateNumber), '^a-z0-9')
                            AND ID<>@ID";

        //parms
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@PlateNumber",plateNumber),
                        new SqlParameter("@ID", id)
                    };

        try
        {
            return (Int16.Parse(SqlHelper.ExecuteScalar(CommandType.Text, strSQL, parms).ToString()) > 0);
        }
        catch (Exception)
        {
            throw;
        }
    }

    public static bool Insert(MonthCard monthCard)
    {
        string insertMonthCard = @"INSERT INTO [MonthCard]
                                   ([ID]
                                   ,[PlateNumber]
                                   ,[PlateTypeId]
                                   ,[CarColorId]
                                   ,[ParkingFloorId]
                                   ,[CustomerId]
                                   ,[FromDate]
                                   ,[ToDate]
                                   ,[Price]
                                   ,[LastUser]
                                   ,[LastUpdate]
                                   ,[Active])
                             VALUES
                                   (@ID
                                   ,@PlateNumber
                                   ,@PlateTypeId
                                   ,@CarColorId
                                   ,@ParkingFloorId
                                   ,@CustomerId
                                   ,@FromDate
                                   ,@ToDate
                                   ,@Price
                                   ,@LastUser
                                   ,GETDATE()
                                   ,1)";
        string insertMonthCardHistory = @"INSERT INTO [MonthCardHistory]
                                       ([ID]
                                       ,[PlateNumber]
                                       ,[PlateTypeId]
                                       ,[CarColorId]
                                       ,[ParkingFloorId]
                                       ,[CustomerId]
                                       ,[FromDate]
                                       ,[ToDate]
                                       ,[Price]
                                       ,[LastUser]
                                       ,[LastUpdate]
                                       ,RefId)
                                 VALUES
                                       (NewId()
                                       ,@PlateNumber
                                       ,@PlateTypeId
                                       ,@CarColorId
                                       ,@ParkingFloorId
                                       ,@CustomerId
                                       ,@FromDate
                                       ,@ToDate
                                       ,@Price
                                       ,@LastUser
                                       ,GETDATE()
                                       ,@ID)";
        object plateTypeId = DBNull.Value;
        if (monthCard.PlateType != null)
        {
            plateTypeId = monthCard.PlateType.ID;
        }

        object carColorId = DBNull.Value;
        if (monthCard.CarColor != null)
        {
            carColorId = monthCard.CarColor.ID;
        }

        object parkingFloorId = DBNull.Value;
        if (monthCard.ParkingFloor != null)
        {
            parkingFloorId = monthCard.ParkingFloor.ID;
        }
        //parm
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@ID",monthCard.ID),
                        new SqlParameter("@PlateNumber",monthCard.PlateNumber),
                        new SqlParameter("@PlateTypeId", plateTypeId),
                        new SqlParameter("@CarColorId", carColorId),
                        new SqlParameter("@ParkingFloorId", parkingFloorId),
                         new SqlParameter("@CustomerId", monthCard.Customer.CustomerID),
                        new SqlParameter("@FromDate",monthCard.FromDate),
                        new SqlParameter("@ToDate",monthCard.ToDate),
                        new SqlParameter("@Price",monthCard.Price),
                        new SqlParameter("@LastUser",monthCard.LastUser.Id)
                    };
        bool flag = false;
        try
        {
            SqlConnection conn = SqlHelper.Connection;
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, insertMonthCard, parms);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, insertMonthCardHistory, parms);
            trans.Commit();
            flag = true;
        }
        catch (Exception)
        {
            throw;
        }
        return flag;
    }

    public static bool Update(MonthCard monthCard)
    {
        string updateMonthCard = @"UPDATE MonthCard
                                       SET [PlateNumber] = @PlateNumber
                                          ,[PlateTypeId] = @PlateTypeId
                                          ,[CarColorId] = @CarColorId
                                          ,[ParkingFloorId] = @ParkingFloorId
                                          ,[FromDate] = @FromDate
                                          ,[ToDate] = @ToDate
                                          ,[Price] = @Price
                                          ,[LastUser] = @LastUser
                                          ,[LastUpdate] = GETDATE()
                                   WHERE
                                       ID=@ID";
        string updateMonthCardHistory = @"UPDATE MonthCardHistory
                                       SET [PlateNumber] = @PlateNumber
                                          ,[PlateTypeId] = @PlateTypeId
                                          ,[CarColorId] = @CarColorId
                                          ,[ParkingFloorId] = @ParkingFloorId
                                          ,[FromDate] = @FromDate
                                          ,[ToDate] = @ToDate
                                          ,[Price] = @Price
                                          ,[LastUser] = @LastUser
                                          ,[LastUpdate] = GETDATE()
                                     WHERE [RefID] = @ID";
        object plateTypeId = DBNull.Value;
        if (monthCard.PlateType != null)
        {
            plateTypeId = monthCard.PlateType.ID;
        }

        object carColorId = DBNull.Value;
        if (monthCard.CarColor != null)
        {
            carColorId = monthCard.CarColor.ID;
        }

        object parkingFloorId = DBNull.Value;
        if (monthCard.ParkingFloor != null)
        {
            parkingFloorId = monthCard.ParkingFloor.ID;
        }
        //parm
        SqlParameter[] parms = new SqlParameter[]{
                        new SqlParameter("@ID",monthCard.ID),
                        new SqlParameter("@PlateNumber",monthCard.PlateNumber),
                        new SqlParameter("@PlateTypeId",plateTypeId),
                        new SqlParameter("@CarColorId",carColorId),
                        new SqlParameter("@ParkingFloorId",parkingFloorId),
                        new SqlParameter("@FromDate",monthCard.FromDate),
                        new SqlParameter("@ToDate",monthCard.ToDate),
                        new SqlParameter("@Price",monthCard.Price),
                        new SqlParameter("@LastUser",monthCard.LastUser.Id)
                    };
        bool flag = false;
        try
        {
            SqlConnection conn = SqlHelper.Connection;
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, updateMonthCard, parms);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, updateMonthCardHistory, parms);
            trans.Commit();
            flag = true;
        }
        catch (Exception)
        {
            throw;
        }
        return flag;
    }

    public static bool Renew(MonthCard monthCard)
    {
        string updateMonthCard = @"UPDATE MonthCard
                                       SET FromDate=@FromDate,
                                       ToDate=@ToDate,
                                       Price=@Price,
                                       LastUser=@LastUser,
                                       LastUpdate=GETDATE(),
                                       Active=1
                                   WHERE
                                       ID=@ID";

        string updateMonthCardHistory = @"UPDATE MonthCardHistory
                                       SET RefId=NewId()
                                   WHERE
                                       RefId=@ID";

        string insertMonthCardHistory = @"INSERT INTO [MonthCardHistory]
                                       ([ID]
                                       ,[PlateNumber]
                                       ,[PlateTypeId]
                                       ,[CarColorId]
                                       ,[ParkingFloorId]
                                       ,[CustomerId]
                                       ,[FromDate]
                                       ,[ToDate]
                                       ,[Price]
                                       ,[LastUser]
                                       ,[LastUpdate]
                                       ,RefId)
                                 VALUES
                                       (NewId()
                                       ,@PlateNumber
                                       ,@PlateTypeId
                                       ,@CarColorId
                                       ,@ParkingFloorId
                                       ,@CustomerId
                                       ,@FromDate
                                       ,@ToDate
                                       ,@Price
                                       ,@LastUser
                                       ,GETDATE()
                                       ,@ID)";      

        object plateTypeId = DBNull.Value;
        if (monthCard.PlateType != null)
        {
            plateTypeId = monthCard.PlateType.ID;
        }

        object carColorId = DBNull.Value;
        if (monthCard.CarColor != null)
        {
            carColorId = monthCard.CarColor.ID;
        }

        object parkingFloorId = DBNull.Value;
        if (monthCard.ParkingFloor != null)
        {
            parkingFloorId = monthCard.ParkingFloor.ID;
        }
        //parm
        SqlParameter[] parms = new SqlParameter[]
        {
                        new SqlParameter("@ID",monthCard.ID),
                        new SqlParameter("@PlateNumber",monthCard.PlateNumber),
                        new SqlParameter("@PlateTypeId", plateTypeId),
                        new SqlParameter("@CarColorId", carColorId),
                        new SqlParameter("@ParkingFloorId", parkingFloorId),
                         new SqlParameter("@CustomerId", monthCard.Customer.CustomerID),
                        new SqlParameter("@FromDate",monthCard.FromDate),
                        new SqlParameter("@ToDate",monthCard.ToDate),
                        new SqlParameter("@Price",monthCard.Price),
                        new SqlParameter("@LastUser",monthCard.LastUser.Id)
        };
        bool flag = false;
        try
        {            
            SqlConnection conn = SqlHelper.Connection;
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();            
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, updateMonthCard, parms);

            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, updateMonthCardHistory, parms);

            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, insertMonthCardHistory, parms);
            trans.Commit();
            flag = true;
        }
        catch (Exception)
        {
            throw;
        }
        return flag;
    }

    public static bool Delete(Guid id)
    {
        bool flag = false;
        string deleteMonthCardHistory = "DELETE FROM MonthCardHistory WHERE RefID=@ID";
        string deleteMonthCard = "DELETE FROM MonthCard WHERE ID=@ID";
        

        //parms
        SqlParameter parm = new SqlParameter("@ID", id);
        SqlTransaction trans = null;
        try
        {
            //sql connect
            SqlConnection conn = SqlHelper.Connection;
            conn.Open();
            trans = conn.BeginTransaction();

            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, deleteMonthCard, parm);
            SqlHelper.ExecuteNonQuery(trans, CommandType.Text, deleteMonthCardHistory, parm);            

            trans.Commit();
            flag = true;
        }
        catch (Exception)
        {
            throw;
        }
        return flag;
    }
}
